package com.jandar.service;

import com.alibaba.fastjson.JSONObject;
import com.jandar.config.AppConfig;
import com.jandar.entity.BankExtraInfo;
import com.jandar.util.DynamicQueryUtil;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.Data;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static com.jandar.constant.QTableConstant.*;


@Service
public class BankService {
    private final JPAQueryFactory queryFactory;
    private final AppConfig appConfig;

    private final DynamicQueryUtil dynamicQueryUtil;

    public BankService(JPAQueryFactory queryFactory, AppConfig appConfig, DynamicQueryUtil dynamicQueryUtil) {
        this.queryFactory = queryFactory;
        this.appConfig = appConfig;
        this.dynamicQueryUtil = dynamicQueryUtil;
    }


    public JSONObject listSheetName() {
        List<String> data = queryFactory.select(qSqlTemplate.name).from(qSqlTemplate).where(qSqlTemplate.type.eq("bank")).fetch();
        return result("", data);
    }

    @Transactional
    public JSONObject pageBySheet(PageParam param) {
        try {
            Thread.sleep(10L);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        Boolean includeSelf = true;
        if (param.type.contains("不含本人")) {
            includeSelf = false;
        }
        createTempTable(param.recordId, param.personId, param.type, includeSelf);
        String sql = queryFactory.select(qSqlTemplate.sqlTemplate).from(qSqlTemplate).where(qSqlTemplate.name.eq(param.type).and(qSqlTemplate.type.eq("bank"))).fetchFirst();
        List<Map<String, Object>> data = dynamicQueryUtil.query(sql.replace("bank_excel_source_temp", "bank_excel_source_temp_" + param.type));
        int size = data.size();
        dropTempTable(param.type);
        if (param.num != null && param.size != null) {
            data = data.stream().skip((long) (param.num - 1) * param.size).limit(param.size).collect(Collectors.toList());
        }
        JSONObject res = result("", data);
        res.put("total", size);
        res.put("name", param.type);
        return res;
    }


    public JSONObject listMappingByBank(String bankName) {
        return result("", dynamicQueryUtil.query("select to_key as toKey, from_key as fromKey, COLUMN_COMMENT as description from map_transfer_mapping a    left join information_schema.columns b on a.to_key = b.COLUMN_NAME where a.bank_name = '#{1}' and b.TABLE_SCHEMA = 'regform' and b.TABLE_NAME = 'bank_excel_source'".replace("#{1}", bankName)));
    }

    public JSONObject listField() {
        List<Map<String, Object>> res = dynamicQueryUtil.query("select COLUMN_NAME, COLUMN_COMMENT\n" +
                "from information_schema.columns\n" +
                "where TABLE_SCHEMA = 'regform'\n" +
                "  and TABLE_NAME = 'bank_excel_source'\n" +
                "and length(COLUMN_COMMENT) > 0\n");
        return result("", res);
    }

//    public JSONObject saveBank(SaveBankParam saveBankParam, int type) {
//        String bankName = saveBankParam.bankName;
//        List<MapTransferMapping> mappingList = saveBankParam.mappingList;
//        if (type == 1) {
//            Long count = queryFactory.select(qMapTransferMapping.count()).from(qMapTransferMapping).where(qMapTransferMapping.bankName.eq(bankName)).fetchOne();
//            if (count > 0) {
//                return result("新增失败，该银行类型已存在");
//            }
//        }
//        deleteBank(bankName);
//        queryFactory.insert(qBankExtraInfo)
//        dslContext.insertInto(BANK_EXTRA_INFO).set(BANK_EXTRA_INFO.SHEET_NUM, saveBankParam.sheetNum).set(BANK_EXTRA_INFO.BANK_NAME, bankName).set(BANK_EXTRA_INFO.START_ROW, saveBankParam.startRow).execute();
//        dslContext.batchInsert(mappingList.stream().map(r -> {
//            MapTransferMappingRecord mapTransferMappingRecord = dslContext.newRecord(MAP_TRANSFER_MAPPING);
//            mapTransferMappingRecord.from(r);
//            mapTransferMappingRecord.setBankName(bankName);
//            return mapTransferMappingRecord;
//        }).collect(Collectors.toList())).execute();
//        return result("保存成功");
//
//
//    }

    public JSONObject deleteBank(String bankName) {
//        queryFactory.delete(qMapTransferMapping).where(qMapTransferMapping.bankName.eq(bankName)).execute();
        queryFactory.delete(qBankExtraInfo).where(qBankExtraInfo.bankName.eq(bankName)).execute();

        return result("删除成功");
    }

    public JSONObject pageBank(PageParam param) {
        int num = param.num;
        int size = param.size;
        List<BankExtraInfo> data = queryFactory.selectFrom(qBankExtraInfo).fetch();
        JSONObject res = result("", data.stream().skip((long) (num - 1) * size).limit(size).collect(Collectors.toList()));
        res.put("total", data.size());

        return res;
    }

//    public JSONObject listBankName() {
//        List<String> data = queryFactory.selectDistinct(qMapTransferMapping.bankName).from(qMapTransferMapping).groupBy(qMapTransferMapping.bankName).fetch();
//        return result("", data);
//    }

//    public void uploadFile(MultipartFile file, String bankName, HttpServletRequest request) {
//        long userId = 0;
//        ValidToken validToken = userService.getValidToken(request);
//        if (validToken.getValid()) {
//            userId = validToken.getUserId();
//        }
//
//        ExcelSourceRecordRecord bankExcelSourceRecordRecord = dslContext.newRecord(EXCEL_SOURCE_RECORD);
//        Integer recordId = 0;
//        try {
//            String filename = file.getOriginalFilename();
//            assert filename != null;
//
//            String[] fileNames = filename.split("\\.");
//            String suffix = fileNames[fileNames.length - 1];
//            String fileName = System.currentTimeMillis() + "-" + fileNames[0] + "." + suffix;
//
//            String savePath = appConfig.getAnalyzeAndStatisticsDir() + fileName;
//            file.transferTo(new File(savePath));
//
//            bankExcelSourceRecordRecord.setName(fileNames[0]);
//            bankExcelSourceRecordRecord.setSaveName(fileName);
//            bankExcelSourceRecordRecord.setStatus("分析中");
////            bankExcelSourceRecordRecord.setBankName(bankName);
//            bankExcelSourceRecordRecord.setUserId(userId);
//            bankExcelSourceRecordRecord.insert();
//            recordId = dslContext.select(BANK_EXCEL_SOURCE_RECORD.ID).from(BANK_EXCEL_SOURCE_RECORD).orderBy(BANK_EXCEL_SOURCE_RECORD.ID.desc()).limit(1).fetchAnyInto(Long.class);
//            bankExcelSourceRecordRecord.setId(recordId);
//            BankExtraInfo bankExtraInfo = dslContext.selectFrom(BANK_EXTRA_INFO).where(BANK_EXTRA_INFO.BANK_NAME.eq(bankName)).fetchOneInto(BankExtraInfo.class);
//            Long sheetNum = bankExtraInfo.getSheetNum();
//            sheetNum = sheetNum == null ? 0 : sheetNum - 1;
//            Long startRow = bankExtraInfo.getStartRow();
//            ExcelReader reader = ExcelUtil.getReader(savePath);
//
//            reader.setSheet(sheetNum);
//
//            List<Map> readAll = reader.read(startRow - 1, startRow, Map.class);
//            List<Map<String, Object>> mappingList = dslContext.selectFrom(MAP_TRANSFER_MAPPING).where(MAP_TRANSFER_MAPPING.BANK_NAME.eq(bankName)).fetchMaps();
//
//            processBankData(readAll, mappingList, bankExcelSourceRecordRecord);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }
//
//    public void processBankDataApi(List<Map> data, List<Map<String, Object>> mappingList) {
//        ExcelSourceRecordRecord bankExcelSourceRecordRecord = dslContext.newRecord(EXCEL_SOURCE_RECORD);
//        Integer recordId = 0;
//        try {
//            String suffix = "xlsx";
//            String name = System.currentTimeMillis() + "-" + "remote";
//            String fileName = name + "." + suffix;
//            String savePath = appConfig.getAnalyzeAndStatisticsDir() + fileName;
//
//            ExcelWriter writer = new ExcelWriter(savePath);
//            writer.write(data, true);
//            writer.close();
//
//            bankExcelSourceRecordRecord.setName(name);
//            bankExcelSourceRecordRecord.setSaveName(fileName);
//            bankExcelSourceRecordRecord.setStatus("已完成");
////            bankExcelSourceRecordRecord.setBankName("remote");
//            bankExcelSourceRecordRecord.insert();
//
//            recordId = dslContext.select(EXCEL_SOURCE_RECORD.ID).from(EXCEL_SOURCE_RECORD).orderBy(EXCEL_SOURCE_RECORD.ID.desc()).limit(1).fetchAnyInto(Long.class);
//            bankExcelSourceRecordRecord.setId(recordId);
//
//            processBankData(data, mappingList, bankExcelSourceRecordRecord);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

//    public void processBankData(List<Map> data, List<Map<String, Object>> mappingList, ExcelSourceRecordRecord bankExcelSourceRecordRecord) {
//        try {
//            Long finalRecordId = bankExcelSourceRecordRecord.getId();
//
//            List<BankExcelSourceRecord> insertList = data.stream().map(item -> {
//                BankExcelSourceRecord b = dslContext.newRecord(BANK_EXCEL_SOURCE);
//
//                Map<String, Object> m = MapTransferUtil.transfer(item, mappingList);
//                try {
//                    if (m.containsKey("send_amount")) {
//                        BigDecimal sendAmount = new BigDecimal(String.valueOf(m.get("send_amount")));
//                        if (sendAmount.longValue() > 0) {
//                            m.put("transaction_amount", sendAmount);
//                            m.put("load_type", "借");
//                        }
//                    } else if (m.containsKey("receive_amount")) {
//                        BigDecimal receiveAmount = new BigDecimal(String.valueOf(m.get("receive_amount")));
//                        if (receiveAmount.longValue() > 0) {
//                            m.put("transaction_amount", receiveAmount);
//                            m.put("load_type", "贷");
//                        }
//                    }
//                } catch (Exception e) {
//                    System.out.println(item);
//                    System.out.println(m);
//                    System.out.println(e.getMessage());
//                }
//
//                b.fromMap(m);
//                b.setRecordId(finalRecordId);
//                return b;
//            }).collect(Collectors.toList());
//            dslContext.batchInsert(insertList).execute();
//
//            // 提取人员信息
//            String sql = "select send_name as name,send_card as number from bank_excel_source where record_id=#recordId and length(send_name) > 0 and length(send_card) > 0 group by send_name,send_card\n";
////                  +  "union all\n" +
////                    "select receive_name as name,receive_card as number from bank_excel_source where record_id=#recordId and length(receive_name) > 0 and length(receive_card) > 0 group by receive_name,receive_card\n" +
////                    "union all\n" +
////                    "select concat(send_name,'-未知转账对象') as name,receive_card as number from bank_excel_source where record_id=#recordId and length(receive_name) = 0 and length(receive_card) > 0 group by send_name, receive_card";
//            sql = sql.replace("#recordId", String.valueOf(finalRecordId));
//            List<Map<String, Object>> personList = dslContext.fetch(sql).intoMaps();
//            personList.forEach(person -> {
//                String name = String.valueOf(person.get("name"));
//                dslContext.execute("INSERT INTO excel_person (name, id_card)\n" +
//                        "values ('" + name + "', '')\n" +
//                        "ON DUPLICATE KEY UPDATE id=id;" +
//                        "update excel_person set is_respondents=1 where name='" + name + "'");
//                StatisticsPersonRecord personRecord = dslContext.selectFrom(STATISTICS_PERSON).where(STATISTICS_PERSON.NAME.eq(name)).fetchAny();
//                Integer personId = personRecord.getId();
//                dslContext.execute("INSERT INTO excel_person_info (type,number,person_id,record_id)\n" +
//                        "values ('银行卡','" + person.get("number") + "', '" + personId + "','" + finalRecordId + "')\n" +
//                        "ON DUPLICATE KEY UPDATE id=id;");
//            });
//            // 通过工具类创建writer
//            AtomicReference<ExcelWriter> writer = new AtomicReference<>(null);
//            sqlMap.forEach((key, value) -> {
//                if (writer.get() == null) {
//                    writer.set(new ExcelWriter(appConfig.getAnalyzeAndStatisticsDir() + "result-" + bankExcelSourceRecordRecord.getSaveName(), key));
//                } else {
//                    writer.get().setSheet(key);
//                }
//
//                createTempTable(finalRecordId, null, key, null);
//                Result<Record> sheet1Result = dslContext.fetch(value.replace("bank_excel_source_temp", "bank_excel_source_temp_" + key));
//                dropTempTable(key);
//                List<Map<String, Object>> sheet1List = sheet1Result.stream().map(item -> {
//                    Map<String, Object> itemMap = item.intoMap();
//                    itemMap.remove("exclude1");
//                    itemMap.remove("exclude2");
//                    itemMap.remove("exclude3");
////                itemMap.entrySet().stream().filter(entry -> entry.getKey().contains("exclude")).forEach(entry -> itemMap.remove(entry.getKey()));
//                    return itemMap;
//                }).collect(Collectors.toList());
//                // 一次性写出内容，使用默认样式，强制输出标题
//                writer.get().write(sheet1List, true);
//            });
//            bankExcelSourceRecordRecord.setStatus("已完成");
//            // 关闭writer，释放内存
//            writer.get().close();
//        } catch (Exception e) {
//            bankExcelSourceRecordRecord.setStatus(e.getMessage());
//            e.printStackTrace();
//        } finally {
//            bankExcelSourceRecordRecord.update();
//        }
//    }
//
//    public File getFile(Integer id, String type) {
//        String fileName = dslContext.select(EXCEL_SOURCE_RECORD.SAVE_NAME).from(EXCEL_SOURCE_RECORD).where(EXCEL_SOURCE_RECORD.ID.eq(id)).fetchAnyInto(String.class);
//        if (type.equals("result")) {
//            fileName = "result-" + fileName;
//        }
//        fileName = appConfig.getAnalyzeAndStatisticsDir() + fileName;
//        return new File(fileName);
//    }


    //    private void createTempTable(Integer recordId, Integer personId, String type, Boolean includeSelf) {
//        String extractCondition = "";
//        if (includeSelf == null) {
//            includeSelf = true;
//        }
//        if (!includeSelf) {
//            extractCondition = " and b.send_name<>b.receive_name";
//        }
//        dropTempTable(type);
//        String sql = "";
//        String tableName = "bank_excel_source_temp_" + type;
//        if (personId == null) {
//            sql = "CREATE TABLE " + tableName + " SELECT * FROM bank_excel_source where record_id=" + recordId + ";";
//        } else if (recordId == null) {
//            sql = "create table " + tableName + " select  distinct p.id as 'ignore', b.*\n" +
//                    "from excel_person_info pi\n" +
//                    "         left join excel_person p\n" +
//                    "                    on pi.person_id = p.id\n" +
//                    "         right join bank_excel_source b\n" +
//                    "             on (pi.number = b.send_card and p.name = b.send_name) or\n" +
//                    "                                           (pi.number = b.receive_card and p.name = b.receive_name)\n" +
//                    " where p.id = " + personId + extractCondition;
//        } else {
//            sql = "create table " + tableName + " select  distinct p.id as 'ignore',  b.*\n" +
//                    "from excel_person_info pi\n" +
//                    "         left join excel_person p\n" +
//                    "                    on pi.person_id = p.id\n" +
//                    "         right join bank_excel_source b\n" +
//                    "             on (pi.number = b.send_card and p.name = b.send_name) or\n" +
//                    "                                           (pi.number = b.receive_card and p.name = b.receive_name)\n" +
//                    "where b.record_id = " + recordId + " and p.id = " + personId + extractCondition;
//        }
//        dynamicQueryUtil.execute(sql);
//    }
    private void createTempTable(Integer recordId, Integer personId, String type, Boolean includeSelf) {
        String extractCondition = includeSelf != null && !includeSelf ? " AND b.send_name <> b.receive_name" : "";
        String tableName = "bank_excel_source_temp_" + type;

        // 创建临时表结构
        createTempTableStructure(tableName);

        String insertSql = "";
        if (personId == null) {
            insertSql = "INSERT INTO " + tableName + " SELECT * FROM bank_excel_source WHERE record_id=" + recordId + ";";
        } else if (recordId == null) {
            insertSql = "INSERT INTO " + tableName + " SELECT DISTINCT  b.*\n" +
                    "FROM excel_person_info pi\n" +
                    "LEFT JOIN excel_person p ON pi.person_id = p.id\n" +
                    "RIGHT JOIN bank_excel_source b ON (pi.number = b.send_card AND p.name = b.send_name) OR (pi.number = b.receive_card AND p.name = b.receive_name)\n" +
                    "WHERE p.id = " + personId + extractCondition;
        } else {
            insertSql = "INSERT INTO " + tableName + " SELECT DISTINCT  b.*\n" +
                    "FROM excel_person_info pi\n" +
                    "LEFT JOIN excel_person p ON pi.person_id = p.id\n" +
                    "RIGHT JOIN bank_excel_source b ON (pi.number = b.send_card AND p.name = b.send_name) OR (pi.number = b.receive_card AND p.name = b.receive_name)\n" +
                    "WHERE b.record_id = " + recordId + " AND p.id = " + personId + extractCondition;
        }

        dynamicQueryUtil.execute(insertSql);
    }

    // 新增方法用于创建临时表结构
    private void createTempTableStructure(String tableName) {
        String sql = "CREATE TABLE " + tableName + " LIKE bank_excel_source;";
        dynamicQueryUtil.execute(sql);
    }

    private void dropTempTable(String type) {
        dynamicQueryUtil.execute("drop table if exists bank_excel_source_temp_" + type);
    }

    private JSONObject result(String msg, Object data) {
        JSONObject res = new JSONObject();
        res.put("status", true);
        res.put("msg", msg);
        res.put("data", data);
        return res;
    }

    private JSONObject result(String msg) {
        JSONObject res = new JSONObject();
        res.put("status", true);
        res.put("msg", msg);
        return res;
    }

    @Data
    public static class PageParam {
        private Integer size;
        private Integer num;
        private String contains;
        private String type;
        private Integer recordId;
        private Integer personId;
    }


}
